package KC.cimaV2;

import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.*;
import java.util.Calendar;
import java.text.SimpleDateFormat;

public class Factura {
    public static int NOCONCILIADA = 1;
    public static int CONCILIADA = 2;
    public static int MANUAL = 1;
    public static int AUTOMATICA = 2;

    private int id_conciliacion;
    private String proveedor;
    private String proveedorNom;
    private String folio;
    private Calendar fecha;
    private String comentarios;
    private double porcentajeMerma;
    private double porcentajeIva;
    private double porcentajeRetencion;
    private double precioMaiz;
    private double subTotal;
    private double iva;
    private double retencion;
    private double total;
    private int estatus;
    private Calendar fecha_sistema;
    private String usuario;

    public Factura(){
        fecha = Calendar.getInstance();
        fecha_sistema = Calendar.getInstance();
    }


/*Obtiene datos de factura y crea instancia
 *   @param Statement select ejecuta consulta SQL
 *   @return Factura
     */

    public Factura(Statement select, int id_conciliacion)throws SQLException{
        String query = "SELECT factura,"+
                            " ISNULL(comentario,'')AS comentario,"+
                            " fecha_factura,"+
                            " ISNULL(proveedor, '') AS codigo_proveedor,"+
                            " ISNULL(vw_p.nombre,'') AS proveedor,"+
                            " pctmerma,"+
                            " pctiva,"+
                            " pctretencion,"+
                            " precio_maiz,"+
                            " subtotal,"+
                            " iva,"+
                            " retencion,"+
                            " total,"+
                            " estatus,"+
                            " usuario,"+
                            " fecha_sistema"+
                        " FROM conciliacion "+
                            " LEFT JOIN baan.dbo.proveedores vw_p ON vw_p.cod_proveedor = conciliacion.proveedor"+
                        " WHERE id_conciliacion = "+id_conciliacion;

        fecha = Calendar.getInstance();
        fecha_sistema = Calendar.getInstance();

        ResultSet registro = select.executeQuery(query);
        if(registro.first()){
            this.id_conciliacion = id_conciliacion;
            folio = registro.getString("factura");
            comentarios = registro.getString("comentario");
            fecha.setTime(registro.getDate("fecha_factura"));
            proveedor = registro.getString("codigo_proveedor");
            proveedorNom = registro.getString("proveedor");
            porcentajeMerma = registro.getDouble("pctmerma");
            porcentajeIva = registro.getDouble("pctiva");
            porcentajeRetencion = registro.getDouble("pctretencion");
            precioMaiz = registro.getDouble("precio_maiz");
            subTotal = registro.getDouble("subtotal");
            iva = registro.getDouble("iva");
            retencion = registro.getDouble("retencion");
            total = registro.getDouble("total");
            estatus = registro.getInt("estatus");
            usuario = registro.getString("usuario");
            fecha_sistema.setTime(registro.getDate("fecha_sistema"));
        }
        registro.close();
        select.close();
    }

    public int getId(){
        return id_conciliacion;
    }

    public void setProveedor(String proveedor){
        this.proveedor = proveedor;
    }

    public String getProveedor(){
        return proveedor;
    }

    public void setProveedorNom(String proveedorNom){
        this.proveedorNom = proveedorNom;
    }

    public String getProveedorNom(){
        return proveedorNom;
    }

    public void setFolio(String folio){
        this.folio = folio;
    }

    public String getFolio(){
        return folio;
    }

    public void setFecha(java.util.Date fecha){
        this.fecha.setTime(fecha);
    }

    public java.util.Date getFecha(){
        return this.fecha.getTime();
    }

    public void setComentarios(String comentarios){
        this.comentarios = comentarios;
    }

    public String getComentarios(){
        return comentarios;
    }

    public void setPorcentajeMerma(double procentajeMerma){
        this.porcentajeMerma = procentajeMerma;
    }

    public double getPorcentajeMerma(){
        return porcentajeMerma;
    }

    public void setPorcentajeIva(double porcentajeIva){
        this.porcentajeIva = porcentajeIva;
    }

    public double getPorcentajeIva(){
        return porcentajeIva;
    }

    public void setPorcentajeRetencion(double porcentajeRetencion){
        this.porcentajeRetencion = porcentajeRetencion;
    }

    public double getPorcentajeRetencion(){
        return porcentajeRetencion;
    }

    public void setPrecioMaiz(double precioMaiz){
        this.precioMaiz = precioMaiz;
    }

    public double getPrecioMaiz(){
        return precioMaiz;
    }

    public void setSubTotal(double subTotal){
        this.subTotal = subTotal;
    }

    public double getSubTotal(){
        return subTotal;
    }

    public void setIva(double iva){
        this.iva = iva;
    }

    public double getIva(){
        return iva;
    }

    public void setRetencion(double retencion){
        this.retencion = retencion;
    }

    public double getRetencion(){
        return retencion;
    }

    public void setTotal(double total){
        this.total = total;
    }

    public double getTotal(){
        return total;
    }

    public void setEstatus(int estatus){
        this.estatus = estatus;
    }

    public int getEstatus(){
        return estatus;
    }

    public void setUsuario(String usuario){
        this.usuario = usuario;
    }

    public String getUsuario(){
        return usuario;
    }

    public static Object[] listado( Statement stmt_listado, int int_pEstatus, String str_factura, String str_proveedor, Calendar cal_sistemaInicio, Calendar cal_sistemaFin, Calendar cal_facturaInicio, Calendar cal_facturaFin )throws SQLException{
        ArrayList arrL_retVal = new ArrayList();
        SimpleDateFormat sdf = new SimpleDateFormat( "yyyy/MM/dd" );
        String str_filtroFechaSistema = "";
        if( cal_sistemaInicio != null && cal_sistemaFin != null ){
            cal_sistemaFin.add(Calendar.DATE, 1);
            str_filtroFechaSistema = " and fecha_sistema between '"+( sdf.format(cal_sistemaInicio.getTime()) )+"' and '"+( sdf.format(cal_sistemaFin.getTime()) )+"' ";
        }else if( cal_sistemaInicio != null ){
            str_filtroFechaSistema = " and fecha_sistema >= '"+(sdf.format(cal_sistemaInicio.getTime()))+"' ";
        }else if( cal_sistemaFin != null ){
            str_filtroFechaSistema = " and fecha_sistema <= '"+(sdf.format(cal_sistemaFin.getTime()))+"' ";
        }
        String str_filtroFechaFactura = "";
        if( cal_facturaInicio != null && cal_facturaFin != null ){
            cal_facturaFin.add(Calendar.DATE, 1);
            str_filtroFechaFactura = " and fecha_factura between '"+( sdf.format(cal_facturaInicio.getTime()) )+"' and '"+( sdf.format(cal_facturaFin.getTime()) )+"' ";
        }else if( cal_facturaInicio != null ){
            str_filtroFechaFactura = " and fecha_factura >= '"+(sdf.format(cal_facturaInicio.getTime()))+"' ";
        }else if( cal_facturaFin != null ){
            str_filtroFechaFactura = " and fecha_factura <= '"+(sdf.format(cal_facturaFin.getTime()))+"' ";
        }
        String str_listado =
                " select c.id_conciliacion, "+
                "        c.factura, " +
                "        c.fecha_factura, " +
                "        c.proveedor, " +
                "        p.nombre as nombreProveedor, " +
                "        c.estatus, " +
                "        c.fecha_sistema, " +
                "        c.subtotal, " +
                "        iva, " +
                "        retencion, " +
                "        total, " +
                "        tipo"+
                " from	 conciliacion c " +
                "                left join sap.dbo.proveedores p " +
                "                on c.proveedor = p.cod_proveedor "+
                " where  1 = 1 "+(int_pEstatus > 0 ? " and c.estatus = "+int_pEstatus+" " : "" )+" "+
                "        "+( str_factura != null && !str_factura.equals("") ? " and c.factura like '%"+str_factura+"%'  " : "" )+" "+
                "        "+( str_proveedor !=null && !str_proveedor.equals("") ? " and ( c.proveedor like '%"+str_proveedor+"%' or p.nombre like '%"+str_proveedor+"%' ) " : "" )+" "+
                "        "+str_filtroFechaSistema+" "+
                "        "+str_filtroFechaFactura+" "+
                " order by proveedor, "+
                "          fecha_factura desc ";
        ResultSet rs_listado = stmt_listado.executeQuery(str_listado);
        String str_codProveedor;
        String str_desProveedor;
        Calendar cal_fechaFactura;
        Calendar cal_fechaSistema;
        while( rs_listado.next() ){
            cal_fechaFactura = Calendar.getInstance();
            cal_fechaFactura.setTime(rs_listado.getDate("fecha_factura"));
            str_codProveedor = rs_listado.getString("proveedor");
            str_desProveedor = rs_listado.getString("nombreProveedor");
            cal_fechaSistema = Calendar.getInstance();
            cal_fechaSistema.setTime(rs_listado.getDate("fecha_sistema"));
            Object [] objs_campos = {
                    new Integer( rs_listado.getInt("id_conciliacion") ),
                    rs_listado.getString("factura"),
                    cal_fechaFactura,
                    str_codProveedor,
                    str_desProveedor,
                    new Integer( rs_listado.getInt("estatus") ),
                    cal_fechaSistema,
                    new Double( rs_listado.getDouble("subtotal") ),
                    new Double( rs_listado.getDouble("iva") ),
                    new Double( rs_listado.getDouble("retencion") ),
                    new Double( rs_listado.getDouble("total") ),
                    new Integer( rs_listado.getInt("tipo") )
            };
            arrL_retVal.add(objs_campos);
        }
        rs_listado.close();
        stmt_listado.close();
        return arrL_retVal.toArray();
    }

    public static String borrar( Statement stmt_borrar, int int_id )throws SQLException{
        String str_retVal = "";

        Connection con = stmt_borrar.getConnection();

        con.setAutoCommit(false);

        String str_update = "UPDATE unidad"+
                            " SET fact_concil = null"+
                            " WHERE fact_concil = "+int_id;

        stmt_borrar.addBatch(str_update);

        String str_borrar = " delete from conciliacion " +
                            " where id_conciliacion = "+int_id+" and " +
                            "        estatus = "+NOCONCILIADA+" ";

        stmt_borrar.addBatch(str_borrar);
        stmt_borrar.executeBatch();
        con.commit();

        try{
            con.rollback();
            con.setAutoCommit(true);
        }catch(SQLException e){}

        return str_retVal;
    }

    /*Inserta factura y detalle
     *@param insert Statement que ejecuta los insert
     *@return true - inserta, false - no inserta
     */
    public boolean nuevo(Statement insert, ArrayList cuales)throws SQLException{
        boolean retorno = false;
        SimpleDateFormat sdf = new SimpleDateFormat( "yyyy/MM/dd" );
            String insert_factura = "INSERT INTO conciliacion" +
                                    " (factura," +
                                    " comentario," +
                                    " fecha_factura," +
                                    " proveedor," +
                                    " pctmerma," +
                                    " pctiva," +
                                    " pctretencion," +
                                    " precio_maiz," +
                                    " subtotal," +
                                    " iva," +
                                    " retencion," +
                                    " total," +
                                    " estatus," +
                                    " usuario," +
                                    " fecha_sistema," +
                                    " tipo)" +
                                    " VALUES ('" + folio + "'," +
                                    "'" + comentarios + "'," +
                                    "'" + sdf.format(fecha.getTime()) + "'," +
                                    " '" + proveedor + "', " +
                                    porcentajeMerma + "," +
                                    porcentajeIva + "," +
                                    porcentajeRetencion + "," +
                                    precioMaiz + "," +
                                    subTotal + "," +
                                    iva + "," +
                                    retencion + "," +
                                    total + "," +
                                    NOCONCILIADA + "," +
                                    "'" + usuario + "'," +
                                    " CONVERT(VARCHAR, GETDATE(), 111)," +
                                    MANUAL + " )";

            insert.getConnection().setAutoCommit(false);
            insert.addBatch(insert_factura);
            if( cuales.size() > 0 ){
            String insert_detalle = "INSERT INTO conciliacion_detalle" +
                                    " (conciliacion," +
                                    " unidad," +
                                    " inicial," +
                                    " numero," +
                                    " guia," +
                                    " fecha," +
                                    " almacen_origen," +
                                    " almacen_destino," +
                                    " importe," +
                                    " iva," +
                                    " retencion," +
                                    " total," +
                                    " ton_descontar," +
                                    " importe_descontar," +
                                    " tarifa," +
                                    " tipo_tarifa," +
                                    " moneda" +
                                    " )";
            String tmp = "";
            String update = "";
            String unidades = "";
            for (int i = 0; i < cuales.size(); i++) {
                Object[] registro = (Object[]) cuales.get(i);
                int id_unidad = ((Integer) registro[0]).intValue();
                double importe = ((Double) registro[1]).doubleValue();
                double iva = ((Double) registro[2]).doubleValue();
                double retencion = ((Double) registro[3]).doubleValue();
                double total = importe + iva - retencion;
                double ton_descontar = ((Double) registro[4]).doubleValue();
                double importe_descontar = ((Double) registro[5]).doubleValue();

                update = "UPDATE unidad" +
                         " SET FACT_CONCIL = (SELECT @@IDENTITY)" +
                         " WHERE id_unidad = " + id_unidad;

                insert.addBatch(update);

                if (i > 0)
                    tmp += " UNION ";
                tmp += " SELECT @@IDENTITY, " +
                        " id_unidad," +
                        " inicial," +
                        " numero," +
                        " no_guia," +
                        " '" + sdf.format(fecha.getTime()) + "'," +
                        " u.almacen_origen," +
                        " u.almacen_destino," +
                        importe + "," +
                        iva + "," +
                        retencion + "," +
                        total + "," +
                        ton_descontar + "," +
                        importe_descontar + "," +
                        " rt.costo," +
                        " t.tipo_tarifa," +
                        " tm.abreviatura AS moneda" +
                        " FROM unidad u" +
                        " LEFT JOIN embarque e ON e.id_emb = u.embarque" +
                        " LEFT JOIN ruta_trans rt ON rt.ruta = e.ruta AND rt.transporte = e.transporte and e.fecha between rt.fecha_inicio and rt.fecha_fin " +
                        " LEFT JOIN transporte t ON t.id_transporte = rt.transporte" +
                        " LEFT JOIN tipo_moneda tm ON tm.id_tipo = rt.tipo_moneda" +
                        " WHERE id_unidad = " + id_unidad;

                unidades += (i > 0 ? "," : "") + id_unidad;
            }

            String update_embarque = "UPDATE unidad" +
                                     " SET proveedor = '" + proveedor + "'" +
                                     " WHERE id_unidad IN (" + unidades + ")";

            insert.addBatch(update_embarque);

            insert_detalle += tmp;
            insert.addBatch(insert_detalle);

            insert.executeBatch();

            insert.getConnection().commit();
            insert.getConnection().setAutoCommit(true);

            ResultSet registro = insert.executeQuery(
                    "SELECT MAX(id_conciliacion) AS id FROM conciliacion");
            registro.first();
            this.id_conciliacion = registro.getInt("id");

            registro.close();

            try {
                insert.getConnection().rollback();
                insert.getConnection().setAutoCommit(true);
            } catch (SQLException es) {
                throw es;
            }
        }

        return retorno;
    }

/*Obtiene unidades de factura
    * @param Statement ejecuta consulta SQL
    * @return areglo de objetos con datos de unidad
     */

    public Object[] getUnidadesFactura(Statement select)throws SQLException{
        ArrayList unidades = new ArrayList();
        String query = "SELECT u.id_unidad," +
                            " u.fecha_recepcion,"+
                            " cd.inicial+'-'+CONVERT(VARCHAR,cd.numero) AS placas,"+
                            " isnull( u.no_guia, '' ) as no_guia, "+
                            " u.ticket_destino, "+
                            " ao.codigo AS almacen_origen,"+
                            " ad.codigo AS almacen_destino,"+
                            " u.peso_origen,"+
                            " u.peso_destino,"+
                            " peso_origen - peso_destino AS diferencia,"+
                            " u.peso_origen*(c.pctmerma/100)AS merma,"+
                            " cd.ton_descontar,"+
                            " c.precio_maiz,"+
                            " cd.importe_descontar,"+
                            " u.peso_destino AS peso_pagar,"+
                            " cd.tarifa,"+
                            " cd.importe,"+
                            " cd.iva,"+
                            " cd.retencion,"+
                            " cd.total"+
                    " FROM conciliacion c"+
                            " INNER JOIN conciliacion_detalle cd ON cd.conciliacion = c.id_conciliacion"+
                            " INNER JOIN unidad u ON u.id_unidad = cd.unidad"+
                            " LEFT JOIN almacen ao ON id_almacen = u.almacen_origen"+
                            " LEFT JOIN almacen ad ON ad.id_almacen = u.almacen_destino"+
                    " WHERE id_conciliacion = "+id_conciliacion;

            ResultSet registros = select.executeQuery(query);
            int id_unidad;
            Calendar fecha = null;
            String placas;
            String str_guia;
            String str_ticket;
            String almacen_origen;
            String almacen_destino;
            double peso_origen;
            double peso_destino;
            double diferencia;
            double merma;
            double ton_descontar;
            double precio_maiz;
            double importe_descontar;
            double peso_pagar;
            double tarifa;
            double importe;
            double iva;
            double retencion;
            double total;
            while(registros.next()){
                id_unidad = registros.getInt("id_unidad");
                fecha = Calendar.getInstance();
                java.util.Date fechaTmp = null;
                fechaTmp = (registros.getDate("fecha_recepcion"));
                if(fechaTmp == null){
                    fecha = null;
                }else {
                    fecha.setTime(fechaTmp);
                }
                placas = registros.getString("placas");
                str_guia = registros.getString("no_guia");
                str_ticket = registros.getString("ticket_destino");
                almacen_origen = registros.getString("almacen_origen");
                almacen_destino = registros.getString("almacen_destino");
                peso_origen = registros.getDouble("peso_origen");
                peso_destino = registros.getDouble("peso_destino");
                diferencia = registros.getDouble("diferencia");
                merma = registros.getDouble("merma");
                ton_descontar = registros.getDouble("ton_descontar");
                precio_maiz = registros.getDouble("precio_maiz");
                importe_descontar = registros.getDouble("importe_descontar");
                peso_pagar = registros.getDouble("peso_pagar");
                tarifa = registros.getDouble("tarifa");
                importe = registros.getDouble("importe");
                iva = registros.getDouble("iva");
                retencion = registros.getDouble("retencion");
                total = registros.getDouble("total");

                Object [] tupla = {
                                  new Integer(id_unidad),
                                  fecha,
                                  placas,
                                  almacen_origen,
                                  almacen_destino,
                                  new Double(peso_origen),
                                  new Double(peso_destino),
                                  new Double(diferencia),
                                  new Double(merma),
                                  new Double(ton_descontar),
                                  new Double(precio_maiz),
                                  new Double(importe_descontar),
                                  new Double(peso_pagar),
                                  new Double(tarifa),
                                  new Double(importe),
                                  new Double(iva),
                                  new Double(retencion),
                                  new Double(total),
                                  str_guia,
                                  str_ticket
                };

                unidades.add(tupla);
            }
            registros.close();
            select.close();
        return unidades.toArray();
    }

/*edita factura
    *@param Statement update ejecuta transaccion SQL,
           ArrayList cuales id de unidades a agregar
    *@return boolean true - edito, false - no edito
     */
    public boolean edita(Statement update, ArrayList cuales)throws SQLException{
        boolean retorno = false;
        SimpleDateFormat sdf = new SimpleDateFormat( "yyyy/MM/dd" );
        String update_factura = "UPDATE conciliacion"+
                                        " SET factura = '"+folio+"',"+
                                        " comentario = '"+comentarios+"',"+
                                        " fecha_factura = '"+sdf.format(fecha.getTime())+"',"+
                                        " proveedor = '"+proveedor+"', "+
                                        " pctmerma = "+porcentajeMerma+","+
                                        " pctiva = "+porcentajeIva+","+
                                        " pctretencion = "+porcentajeRetencion+","+
                                        " precio_maiz = "+precioMaiz+","+
                                        " subtotal = "+subTotal+","+
                                        " iva = "+iva+","+
                                        " retencion = "+retencion+","+
                                        " total = "+total+","+
                                        " usuario = '"+usuario+"',"+
                                        " fecha_sistema = CONVERT(VARCHAR, GETDATE(), 111)"+
                               " WHERE id_conciliacion = "+id_conciliacion;


            update.getConnection().setAutoCommit(false);
            update.addBatch(update_factura);


            // deja en NULL campo de FACT_CONCIL para unidades de conciliacion
            String delete_detalle = " UPDATE unidad"+
                                        " SET FACT_CONCIL = NULL"+
                                        " WHERE id_unidad IN (SELECT unidad FROM conciliacion_detalle WHERE conciliacion = "+id_conciliacion+")";

            update.addBatch(delete_detalle);

            // elimina detalle de conciliacion
            delete_detalle = " DELETE conciliacion_detalle"+
                                " WHERE conciliacion = "+id_conciliacion;

            update.addBatch(delete_detalle);
            if( cuales.size() > 0 ){
                // inserta detalle para conciliacion
                String update_detalle = "INSERT INTO conciliacion_detalle"+
                                                " (conciliacion,"+
                                                " unidad,"+
                                                " inicial,"+
                                                " numero,"+
                                                " guia,"+
                                                " fecha,"+
                                                " almacen_origen,"+
                                                " almacen_destino,"+
                                                " importe,"+
                                                " iva,"+
                                                " retencion,"+
                                                " total,"+
                                                " ton_descontar,"+
                                                " importe_descontar,"+
                                                " tarifa,"+
                                                " tipo_tarifa,"+
                                                " moneda"+
                                                " )";
                        String tmp = "";
                        String updateU = "";
                        for (int i = 0; i < cuales.size(); i++) {
                      Object [] registro = (Object [])cuales.get(i);
                      int id_unidad = ((Integer)registro[0]).intValue();
                      double importe = ((Double)registro[1]).doubleValue();
                      double iva = ((Double)registro[2]).doubleValue();
                      double retencion = ((Double)registro[3]).doubleValue();
                      double total = importe + iva - retencion;
                      double ton_descontar = ((Double)registro[4]).doubleValue();
                      double importe_descontar = ((Double)registro[5]).doubleValue();

                      // inserta id de conciliacion para unidad en detalle
                      updateU = "UPDATE unidad"+
                                    " SET FACT_CONCIL = (SELECT "+id_conciliacion+")"+
                               " WHERE id_unidad = "+id_unidad;

                      update.addBatch(updateU);

                      if(i > 0)
                          tmp += " UNION ";
                      tmp += " SELECT "+id_conciliacion+", "+
                                    " id_unidad,"+
                                    " inicial,"+
                                    " numero,"+
                                    " no_guia,"+
                                    " '"+sdf.format(fecha.getTime())+"',"+
                                    " u.almacen_origen,"+
                                    " u.almacen_destino,"+
                                    importe+","+
                                    iva+","+
                                    retencion+","+
                                    total+","+
                                    ton_descontar+","+
                                    importe_descontar+","+
                                    " rt.costo,"+
                                    " t.tipo_tarifa,"+
                                    " tm.abreviatura AS moneda"+
                            " FROM unidad u"+
                                    " LEFT JOIN embarque e ON e.id_emb = u.embarque "+
                                    " LEFT JOIN ruta_trans rt ON rt.ruta = e.ruta AND rt.transporte = e.transporte and u.fecha_carga between rt.fecha_inicio and rt.fecha_fin "+
                                    " LEFT JOIN transporte t ON t.id_transporte = rt.transporte"+
                                    " LEFT JOIN tipo_moneda tm ON tm.id_tipo = rt.tipo_moneda"+
                            " WHERE id_unidad = "+id_unidad;

                 }
            update_detalle += tmp;
            update.addBatch(update_detalle);
          }
            update.executeBatch();

            update.getConnection().commit();
            update.getConnection().setAutoCommit(true);

            try{
                update.getConnection().rollback();
                update.getConnection().setAutoCommit(true);
            }catch(SQLException es){}


        return retorno;
    }

/*guarda facturas leidas de archivo
    *@param Statement insert ejecuta transaccion SQL
    @return String mensaje de error o exito
     */
    public static String subirFacturas(Statement insert, ArrayList facturas, String usuario)throws SQLException{
        SimpleDateFormat sdf = new SimpleDateFormat( "yyyy/MM/dd" );
        SimpleDateFormat sdfRequest = new SimpleDateFormat( "dd/MM/yyyy" );

        String retorno = "";
        String factura;
        String fecha;
        String importe;
        String iva;
        String total;
        String importeTotal;
        String ivaTotal;
        String totalTotal;
        String moneda;
        String factura_anterior = "";

        String inicial;
        String numero;
        String guia;
        String fechaUnidad;
        String origen;
        String destino;

        String tmp = "";
        String query_detalle = "";

    try{
            insert.getConnection().setAutoCommit(false);
            for(int i=0;i<facturas.size();i++){
                        Object [] registro = (Object[])facturas.get(i);
                        inicial = (String)registro[0];
                        numero = (String)registro[1];
                        guia = (String)registro[2];
                        fechaUnidad = (String)registro[3];
                        origen = (String)registro[4];
                        destino = (String)registro[5];

                        factura = (String)registro[6];
                        fecha = (String)registro[7];
                        importe = (String)registro[8];
                        iva = (String)registro[9];
                        total = (String)registro[10];
                        moneda = (String)registro[11];

                        if(!factura_anterior.equals(factura)){
                            factura_anterior = factura;
                            if(query_detalle.length()>0){
                                query_detalle += tmp;
                                insert.addBatch(query_detalle);
                                tmp = "";
                            }

                            importeTotal = (String)registro[13];
                            ivaTotal = (String)registro[14];
                            totalTotal = (String)registro[15];

                            String query = "INSERT INTO conciliacion"+
                                                " (factura,"+
                                                " fecha_factura,"+
                                                " proveedor,"+
                                                " pctmerma,"+
                                                " pctiva,"+
                                                " pctretencion,"+
                                                " precio_maiz,"+
                                                " subtotal,"+
                                                " iva,"+
                                                " retencion,"+
                                                " total,"+
                                                " estatus,"+
                                                " usuario,"+
                                                " fecha_sistema,"+
                                                " comentario,"+
                                                " tipo)"+
                                          " VALUES ('"+factura+"','"+
                                                    fecha+"',"+
                                                    " null,"+
                                                    " .0,"+
                                                    " .0,"+
                                                    " .0,"+
                                                    " .0,"+
                                                    importeTotal+","+
                                                    ivaTotal+","+
                                                    " .0,"+
                                                    totalTotal+","+
                                                    NOCONCILIADA+","+
                                                    "'"+usuario+"',"+
                                                    " CONVERT(VARCHAR, GETDATE(), 111),"+
                                                    " ''," +
                                                    AUTOMATICA+" )";

                            query_detalle = "INSERT INTO conciliacion_detalle"+
                                                " (conciliacion,"+
                                                " unidad,"+
                                                " inicial,"+
                                                " numero,"+
                                                " guia,"+
                                                " fecha,"+
                                                " almacen_destino,"+
                                                " almacen_origen,"+
                                                " importe,"+
                                                " iva,"+
                                                " retencion,"+
                                                " total,"+
                                                " ton_descontar,"+
                                                " importe_descontar,"+
                                                " tarifa,"+
                                                " tipo_tarifa,"+
                                                " moneda)"+
                                        " SELECT @@IDENTITY,"+
                                                " NULL,'"+
                                                inicial+"','"+
                                                numero+"','"+
                                                guia+"','"+
                                                fechaUnidad+"','"+
                                                destino+"','"+
                                                origen+"',"+
                                                importe+","+
                                                iva+","+
                                                " .0,"+
                                                total+","+
                                                " .0,"+
                                                " .0,"+
                                                " .0,"+
                                                " .0,'"+
                                                moneda+"'";

                            insert.addBatch(query);
                        }else{// if de factura diferente
                            tmp += " UNION SELECT @@IDENTITY,"+
                                            " NULL,'"+
                                            inicial+"','"+
                                            numero+"','"+
                                            guia+"','"+
                                            fechaUnidad+"','"+
                                            destino+"','"+
                                            origen+"',"+
                                            importe+","+
                                            iva+","+
                                            " .0,"+
                                            total+","+
                                            " .0,"+
                                            " .0,"+
                                            " .0,"+
                                            " .0,'"+
                                            moneda+"'";
                        }
                        if (i == facturas.size() - 1) {
                            query_detalle += tmp;
                            insert.addBatch(query_detalle);
                        }
            }

            insert.executeBatch();
            insert.getConnection().commit();
            insert.getConnection().setAutoCommit(true);
            retorno = "Facturas guardadas correctamente";
    }catch(SQLException e){
        retorno = "Error: "+e.toString().replaceAll("'","");
                try{

                    insert.getConnection().rollback();
                    insert.getConnection().setAutoCommit(true);
                }catch(SQLException er){}
                throw e;
    }
        return retorno;
    }

    /* obtiene unidades de conciliacion
     *@param Statement select ejecuta consulta
     *@return areglo de objetos cada objeto es un arreglo de objetos (tupla)
     */
    public Object [] getUnidadesConciliacion(Statement select, int id_detalle)throws SQLException{
        ArrayList registros = new ArrayList();
        String query = "SELECT id_conciliacion_detalle,"+
                                " id_conciliacion,"+
                                " unidad,"+
                                " fact_concil,"+
                                " unidad_sellos,"+
                                " unidad_fecha_recepcion,"+
                                " unidad_origenTFM,"+
                                " unidad_origen,"+
                                " unidad_destinoTFM,"+
                                " unidad_destino,"+
                                " unidad_costo,"+
                                " conciliacion_inicial,"+
                                " conciliacion_numero,"+
                                " conciliacion_guia,"+
                                " conciliacion_origenTFM,"+
                                " conciliacion_origen,"+
                                " conciliacion_destinoTFM,"+
                                " conciliacion_destino,"+
                                " conciliacion_importe"+
                        " FROM vw_unidad_conciliacion"+
                        " WHERE id_conciliacion = "+id_conciliacion+
                                (id_detalle!=-1?" AND id_conciliacion_detalle = "+id_detalle:"");

        ResultSet resultado = select.executeQuery(query);

        int id_conciliacion_detalle;
        int id_unidad;
        int fact_concil;
        String sellos;
        int unidad_origenTFM;
        String unidad_origen;
        int unidad_destinoTFM;
        String unidad_destino;
        double unidad_costo;
        String inicial;
        String numero;
        String guia;
        int origenTFM;
        String origen;
        int destinoTFM;
        String destino;
        double importe;

        while(resultado.next()){
            id_conciliacion_detalle = resultado.getInt("id_conciliacion_detalle");
            id_unidad = resultado.getInt("unidad");
            fact_concil = resultado.getInt("fact_concil");
            sellos = resultado.getString("unidad_sellos");
            java.util.Date fechaTmp = resultado.getDate("unidad_fecha_recepcion");
            Calendar fecha_recepcion = null;
            if(fechaTmp == null){
                fecha_recepcion = null;
            }else{
                fecha_recepcion = Calendar.getInstance();
                fecha_recepcion.setTime(fechaTmp);
            }
            unidad_origenTFM = resultado.getInt("unidad_origenTFM");
            unidad_origen = resultado.getString("unidad_origen");
            unidad_destinoTFM = resultado.getInt("unidad_destinoTFM");
            unidad_destino = resultado.getString("unidad_destino");
            unidad_costo = resultado.getDouble("unidad_costo");
            inicial = resultado.getString("conciliacion_inicial");
            numero = resultado.getString("conciliacion_numero");
            guia = resultado.getString("conciliacion_guia");
            origenTFM = resultado.getInt("conciliacion_origenTFM");
            origen = resultado.getString("conciliacion_origen");
            destinoTFM = resultado.getInt("conciliacion_destinoTFM");
            destino = resultado.getString("conciliacion_destino");
            importe = resultado.getDouble("conciliacion_importe");

            Object[] row = {new Integer(id_conciliacion_detalle), new Integer(id_unidad), new Integer(fact_concil),
                           sellos, fecha_recepcion, new Integer(unidad_origenTFM), unidad_origen, new Integer(unidad_destinoTFM),
                           unidad_destino, new Double(unidad_costo), inicial, numero, guia, new Integer(origenTFM), origen,
                           new Integer(destinoTFM), destino, new Double(importe)};

            registros.add(row);
        }
        resultado.close();
        select.close();
        return registros.toArray();
    }

    /*concilia facturas automaticas
    *@param Statement update ejecuta transaccion
    *@return retorno mensaje de exito o error
    */
    public String conciliacionAutomatica(Statement update, String [] cuales)throws SQLException {
        String retorno;
        String detalles = Util.arrayToString(cuales);
        String update_conciliacion = "UPDATE conciliacion"+
                                     " SET estatus ="+Factura.CONCILIADA+
                                     " WHERE id_conciliacion ="+id_conciliacion;

        String update_detalle = "UPDATE conciliacion_detalle"+
                                      " SET unidad = u.id_unidad"+
                                " FROM unidad u"+
                                      " LEFT JOIN conciliacion_detalle cd ON u.inicial = cd.inicial"+
                                             " AND u.numero = cd.numero"+
                                             " AND u.no_guia = cd.guia"+
                                " WHERE cd.id_conciliacion_detalle IN ("+detalles+")";

        String update_unidad = " UPDATE unidad"+
                               " SET fact_concil = "+id_conciliacion+
                               " FROM conciliacion_detalle cd"+
                                     " LEFT JOIN unidad u ON u.inicial = cd.inicial"+
                                            " AND u.numero = cd.numero"+
                                            " AND u.no_guia = cd.guia"+
                               " WHERE id_conciliacion_detalle IN ("+detalles+")";

        try{
            update.getConnection().setAutoCommit(false);
            update.addBatch(update_conciliacion);
            update.addBatch(update_detalle);
            update.addBatch(update_unidad);
            update.executeBatch();
            update.getConnection().commit();
            retorno = "Factura conciliada";
        }catch(SQLException e){
            retorno = "Error: No se pudo conciliar";
            try{
                update.getConnection().rollback();
                update.getConnection().setAutoCommit(true);
            }catch(SQLException er){}
            throw e;
        }
        update.close();
        return retorno;
    }

/*concilia facturas manuales
     @param Statement update ejecuta transaccion SQL
     @return String mensaje de exito o error
     */
    public String conciliacionManual(Statement update)throws SQLException{
        String retorno;
        String update_conciliacion = "UPDATE conciliacion"+
                                     " SET estatus ="+Factura.CONCILIADA+
                                     " WHERE id_conciliacion ="+id_conciliacion;

        String update_unidad = " UPDATE unidad"+
                               " SET fact_concil = "+id_conciliacion+
                               " FROM conciliacion_detalle cd"+
                                     " LEFT JOIN unidad u ON u.id_unidad = cd.unidad"+
                               " WHERE conciliacion = "+id_conciliacion;

        try{
            update.getConnection().setAutoCommit(false);
            update.addBatch(update_conciliacion);
            update.addBatch(update_unidad);
            update.executeBatch();
            update.getConnection().commit();
            retorno = "Factura conciliada";
        }catch(SQLException e){
            retorno = "Error: No se pudo conciliar";
            try{
                update.getConnection().rollback();
                update.getConnection().setAutoCommit(true);
            }catch(SQLException er){}
            throw e;
        }
        update.close();
        return retorno;
    }

/*obtiene datos de unidad cuando no tiene errores para conciliar
    @param Statement select ejecuta consulta SQL, int id_detalle id de detalle a consultar
    @return String [] datos de unidad
     */
    public static String [] getDetalleSinError(Statement select, int id_detalle)throws SQLException{
        String [] registro = null;
        String query = "SELECT id_unidad,"+
                       "inicial, " +
                       "numero, " +
                       "sellos, " +
                       "observac_sellos, " +
                       "peso_origen, " +
                       "peso_destino, " +
                       "ticket_destino, " +
                       "fecha_recepcion, " +
                       "humedad_origen," +
                       "humedad_destino, " +
                       "observaciones, " +
                       "guia, " +
                       "fecha_carga, " +
                       "almacen_origen," +
                       "almacen_destino, " +
                       "fecha_pronostico," +
                       "CONCIL," +
                       "fecha_embarque," +
                       "transporte," +
                       "factura," +
                       "fecha_factura," +
                       "lote," +
                       "anio," +
                       "codigo_baan" +
                       " FROM vw_conciliacion_detalle"+
                       " WHERE id_conciliacion_detalle = "+id_detalle;

        ResultSet result = select.executeQuery(query);
        try{
            result.first();
            registro = new String[]{result.getString("id_unidad"),
                       result.getString("inicial"),
                       result.getString("numero"),
                       result.getString("sellos"),
                       result.getString("observac_sellos"),
                       result.getString("peso_origen"),
                       result.getString("peso_destino"),
                       result.getString("ticket_destino"),
                       result.getString("fecha_recepcion"),
                       result.getString("humedad_origen"),
                       result.getString("humedad_destino"),
                       result.getString("observaciones"),
                       result.getString("guia"),
                       result.getString("fecha_carga"),
                       result.getString("almacen_origen"),
                       result.getString("almacen_destino"),
                       result.getString("fecha_pronostico"),
                       result.getString("CONCIL"),
                       result.getString("fecha_embarque"),
                       result.getString("transporte"),
                       result.getString("factura"),
                       result.getString("fecha_factura"),
                       result.getString("lote"),
                       result.getString("anio"),
                       result.getString("codigo_baan")
            };
            select.close();
        }catch(SQLException e){throw e;}
        return registro;
    }

    public static String desconciliar(Statement update, int id_conciliacion)throws SQLException{
        String retorno = "";
        String updateConciliacion = "UPDATE conciliacion"+
                        " SET estatus = "+Factura.NOCONCILIADA+
                        " WHERE id_conciliacion = "+id_conciliacion;

        String updateUnidad = "UPDATE unidad"+
                              " SET fact_concil = NULL"+
                              " FROM conciliacion_detalle cd"+
                                   " INNER JOIN unidad u ON u.id_unidad = cd.unidad"+
                              " WHERE conciliacion = "+id_conciliacion;

        try{
            update.getConnection().setAutoCommit(false);
            update.addBatch(updateConciliacion);
            update.addBatch(updateUnidad);
            update.executeBatch();
            update.getConnection().commit();
            update.getConnection().setAutoCommit(true);
            retorno = "Factura desconciliada";
        }catch(SQLException e){
            try{
                retorno = "Error: "+e;
                update.getConnection().rollback();
                update.getConnection().setAutoCommit(true);
            }catch(SQLException er){}
            throw e;
        }

        return retorno;
    }
}
